#!usr/bin/env python3
# -*- encoding: utf-8 -*-
#通过xlrd读取Excel xlwt写Excel
#根据H5的配置表，把配置表转换成json

import xlrd,xlwt
from collections import defaultdict
import os
import time
import json
import re
import math

#api http://xlrd.readthedocs.io/en/latest/api.html#module-xlrd
def read_to_json(excel_path,fieldRow=1,dataRow:int=2,dataFolder:str="",sheetSearchReStr:str="^t_",encoding='utf8'):
    '''
    
    '''
    st = time.clock()   

    try:
        data = xlrd.open_workbook(excel_path) #指定覆盖编码读取 ,encoding_override="utf8"        
    except Exception as e:
        print("读取 %s 出现异常:"%excel_path,e)
    tables = data.sheets() #读取所有表
    reObj = None
    if sheetSearchReStr:
        reObj = re.compile(r"{0}".format(sheetSearchReStr))
        
    #tableNames = [ sheet.name for sheet in tables if not reObj or reObj.match(sheet.name) ]
    c = 0
    datas= defaultdict(list)    
    for sheet in tables:        
        if reObj and not reObj.match(sheet.name):
            #print("不需要对表格 {0} 生成json".format(sheet.name))
            continue
        c+=1
        sheetName = sheet.name #表名        
        fieldNames = [ cell.value for cell in sheet.row(fieldRow)] #第二行为字段名    
        nrows = sheet.nrows #总行数   
        for i in range(dataRow,nrows): #第三行开始是真实数据
            rowData = sheet.row(i) #该行的数据            
            #print('rowData:',type(rowData))
            cellData = {}
            for cell,fieldName in zip(rowData,fieldNames): #zip是顶级函数
                #print("(isinstance(cell.value,int) and cell.value>0)",(isinstance(cell.value,(float,int)) and cell.value>0))
                if fieldName!="" and ((isinstance(cell.value,str) and cell.value!="") or (isinstance(cell.value,(float,int)) and cell.value>0)):    
                    val = cell.value
                    if isinstance(val,(float,int)) and math.floor(val)==val:
                        val = math.floor(val)                        
                    cellData[fieldName] = val
                                        
            datas[sheetName].append(cellData)

        if dataFolder:#每个表生成一个json文件 
            #dataFolder = os.path.split(excel_path)[0]
            jsonFile = os.path.join(dataFolder,sheetName+'.json')
            if os.path.exists(jsonFile):
                os.remove(jsonFile) #删除文件 或者 unlink 函数
            #os.rmdir(jsonFile) #只能删除目录
            #记得要以utf8编码打开文件 否则中文会有乱码
            # 如果之前已经生成过有乱码的文件,然再改成utf8格式读取还是会乱码,需要删掉原来的文件
            with open(jsonFile,'w',encoding='utf8') as f:                 
                json.dump(datas[sheetName],f,indent=2,ensure_ascii=False) #可以通过 indent 字段设置格式化输出 值可以选从0开始有不同的缩进级别选择
        
        print("对表格 {0} 处理完成".format(sheetName))

    et = time.clock()
    log = '对 %s 文件的 %s 个表进行处理耗时 %.3f 秒'%(excel_path,str(c),(et-st))
    print(log)
    return datas,log